<?php


/**
 * imysql-database connection
 * 
 * Query-builders:
 * 		@method selectQuery() 
 *		@method insertQuery
 *		@method deleteQuery
 *
 * Database-functions:
 *		@method registerUser
 *		@method updateSettingValue
 *		@method selectQueryLastUpdate
 *		@method getSettingValue
 *		@method getLatestTimestamp
 *
 * Aid-functions:
 *		@method Sanitize
 *		@method SanitizeForSQL
 *		@method StripSlashes
 *		@method HandleError
 *		@method countRows
 * 
 *
 * @author Jiles
 */

class databaseconnection {
    
    private $mysqli;
    private $usertable = "user";
    private $error_message = "";

    public function  __construct($server, $username, $password, $dbname, $dbport)
    {
        $this->mysqli = new mysqli($server, $username, $password, $dbname, $dbport);
		
			
        if ($this->mysqli->connect_error) {
            die('Connect Error (' . $this->mysqli->connect_errno . ') '
                    . $this->mysqli->connect_error);
        }

    }

    private function connect(){
        $connect = $this->mysqli->connect();
        
        if ($this->mysqli->connect_error) {
            die('Connect Error (' . $this->mysqli->connect_errno . ') '
                    . $this->mysqli->connect_error);
        }
    }

    public function __destruct() {
        $this->mysqli->close()
            OR die("There was a problem disconnecting from the database.");
    }
    
    /* QUERY BUILDERS */
    
    /**
     *
     * @param string $table
     * @param array $columns
     * @param array $whereclause (key = column; value = value)
     * @param bool $singlestring : if return has to be singlestring, set true
     *
     * @return resultSet or string 
     */
    public function selectQuery($table, $columns = null, $whereclause = null, $singlestring = false){

        $query = "SELECT ";
		
        // build columns
        if($columns == null)
            $query .= "* ";
        else
        {
            $size = sizeof($columns);

            for($i = 0; $i < $size; $i++)
            {
                $query .= $columns[$i] . " ";

                if($i++ != ($size - 1))
                    $query .= ", ";
            }
        }

        $query .= "FROM " .$table;

        //build WHERE clause
        if($whereclause != null)
        {
            $query .= " WHERE ";

            $size2 = sizeof($whereclause);
            $i2 = 0;
            foreach($whereclause as $key => $value)
            {
                $query .= "". $key ." = '". $value ."'";
                
                if($i2++ != ($size2 - 1))
                    $query .= " && ";
            }
        }
	
        $resultset = $this->mysqli->query($query)
        	or die("Cannot execute query:selectQuery");
        
        $rows = $resultset->num_rows;
        
        //NO rows selected
        if($rows == 0)
        	return false;
        	
        //if there is only 1 row && only asked for one value &singlestring is true: return string
        if($rows == 1 && sizeof($columns) == 1 && $singlestring){
        	$string = $resultset->fetch_object()->$columns[0];
        	return $string;
        }
        else
       		return $resultset;
        
		

    }
    /**
     *
     * @param string $table
     * @param array $values
     * @param array $columns
     * @param bool returnid: set true to return auto_increment id (one row)
     *
     * @return array/string resultset/result;
     */
    public function insertQuery($table, $values, $columns = null, $returnid = false) {
        
        $query = "INSERT INTO " . $table;

        //build columns
        if( $columns != null){
            $query .= "(";
            
            $size = sizeof($columns);
            $i = 0;
            foreach($columns as $value)
            {
                $query .= $value;
                
                if($i++ != ($size - 1))
                    $query .= ", ";
            }
            
            $query .= ")";
            
        }

        //build values
        if( $values != null){
            $query .= " VALUES ('";

            $size = sizeof($values);
            $i = 0;
            foreach($values as $value)
            {
                $query .= $value;

                if($i++ != ($size - 1))
                    $query .= "', '";
            }

            $query .= "')";

        }
		
        $result = $this->mysqli->query($query);

		//if mysql error occured
        if(!$result)
             $result = $this->mysqli->error;
        
        
        if($returnid)
        	return $this->mysqli->insert_id;
        	
        //affected rows!
        return $result;


    }
    /**
     *
     * @param <string> $table
     * @param <array> $whereclause
     */
    public function deleteQuery($table, $whereclause) {


        $query = "DELETE FROM ". $table . " ". $whereclause;
        
        if(!$this->mysqli->query($query))
             echo $this->mysqli->error;

    }
    
    public function countRows($table, $columns, $whereclause)
    {
    	$query = "SELECT ";
		
        // build columns
        if($columns == null)
            $query .= "* ";
        else
        {
            $size = sizeof($columns);

            for($i = 0; $i < $size; $i++)
            {
                $query .= $columns[$i] . " ";

                if($i++ != ($size - 1))
                    $query .= ", ";
            }
        }

        $query .= "FROM " .$table;

        //build WHERE clause
        if($whereclause != null)
        {
            $query .= " WHERE ";

            $size2 = sizeof($whereclause);
            $i2 = 0;
            foreach($whereclause as $key => $value)
            {
                $query .= "". $key ." = '". $value ."'";
                
                if($i2++ != ($size2 - 1))
                    $query .= " && ";
            }
        }
	
        $resultset = $this->mysqli->query($query)
        	or die("Cannot execute query:countRows()");
        
        $rows = $resultset->num_rows;
        
        return $rows;
    
    }
    
    /* DATABASE FUNCTIONS */

    
    /* Standard Queries */
    /**
    *	@param string $name: name of value wanted
    *
    *	@return value
    */	
    public function getSettingValue($name){
    	$column = array("value");
    	$whereclause = array("name" => $name);
    	$singlestring = true;
    	return $this->selectQuery("settings", $column, $whereclause, $singlestring);
    }
    /**
    *	@param string $setting: name of setting
    *	@param string $newvalue: new value of setting
    *
    *	@return affected rows
    */	
    public function updateSettingValue($setting, $newvalue){
        	
    	$query = "UPDATE settings ";
    	$query .= "SET value = '" .$newvalue ."'";
    	$query .= " WHERE name = '".$setting ."'";
    	
    	$resultset = $this->mysqli->query($query)
        	or die("Cannot execute query:updateSettingValue");
        
        $rows = $resultset->num_rows;
        
        return $rows;
    }
    /**
    *	Add new user to databse (facebookid + facebookname)
    *
    *	@param string $fbid: facebook id
    *	@param string $name: facebook name
    *
    *	@return affected rows
    */
    public function registerUser($fbid, $name){
    
    	$dbresult = $this->checkIfUserIsRegistered($fbid);
    	
    	if($dbresult)
    		return true;
  		else{
  			$values = array(
	    		intval($fbid),
	    		$name
	    	);
	    	
	    	$columns = array(
	    		"fbid",
	    		"username"
	    	);
	    	
	    	return $this->insertQuery("user", $values, $columns);
	  	}
    	
    }
    /**
    *	@return false if user is not registered
    */
    private function checkIfUserIsRegistered($fbid)
    {
    	//selectQuery return false if no rows are selected, otherwise returns the row in resultset
    	return $this->selectQuery("user", null , array("fbid" => $fbid));
    	   	
    }
    /**
    *	check for latest database-timestamp (automatic stamp on update SQL-table:movie_ranking_time)
    *
    *	@return timestamp-string (unix)
    */
    public function getLatestTimestamp(){
    	$query = "SELECT timestamp";
    	$query .= " FROM movie_ranking_time";
    	$query .= " WHERE timestamp = (SELECT MAX(timestamp) FROM movie_ranking_time)";
    	
    	$resultset = $this->mysqli->query($query)
        	or die("Cannot execute query 'getLatestTimestamp()'");
        	
        $string = $resultset->fetch_object()->timestamp;
        return $string;
    }
    /**
    *	Get latest rankings from movies
    *
    *	@return ranking of movie
    */
    public function selectQueryLastUpdate($table, $column, $whereclause)
    {
    	$query = "SELECT " .$column;
    	$query .= " FROM " .$table;
    	$query .= " WHERE timestamp = (SELECT MAX(timestamp) FROM " .$table;
    	
    	//build WHERE clause
        if($whereclause != null)
        {
            $query .= " WHERE ";

            $size = sizeof($whereclause);
            $i = 0;
            foreach($whereclause as $key => $value)
            {
                $query .= "". $key ." = '". $value ."'";
                
                if($i++ != ($size - 1))
                    $query .= " && ";
            }
        }
		
		$query .= ")";
		
		//build WHERE clause
        if($whereclause != null)
        {
        	$query .= " &&";
            
            $size = sizeof($whereclause);
            $i = 0;
            foreach($whereclause as $key => $value)
            {
                $query .= " ". $key ." = '". $value ."'";
                
                if($i++ != ($size - 1))
                    $query .= " && ";
            }
        }
		
		
    	$resultset = $this->mysqli->query($query)
        	or die("Cannot execute query:selectQueryLastUpdate()");
        
        $rows = $resultset->num_rows;
      
        
        //NO rows selected
        if($rows == 0)
        	return false;
        	
        //if there is only 1 row: return string
        if($rows == 1){
        	$string = $resultset->fetch_object()->$column;
        	return $string;
        }
        else
       		return $resultset;
        
    }
    /* AID METHODS */

    private function SanitizeForSQL($str)
    {
        $this->connect();

        if( function_exists( "mysql_real_escape_string" ) )
        {
              $ret_str = mysql_real_escape_string( $str );
        }
        else
        {
              $ret_str = addslashes( $str );
        }
        return $ret_str;
    }

    /*
    Sanitize() function removes any potential threat from the
    data submitted. Prevents email injections or any other hacker attempts.
    if $remove_nl is true, newline chracters are removed from the input.
    */
    private function Sanitize($str,$remove_nl=true)
    {
        $str = $this->StripSlashes($str);

        if($remove_nl)
        {
            $injections = array('/(\n+)/i',
                '/(\r+)/i',
                '/(\t+)/i',
                '/(%0A+)/i',
                '/(%0D+)/i',
                '/(%08+)/i',
                '/(%09+)/i'
                );
            $str = preg_replace($injections,'',$str);
        }

        return $str;
    }
    
    private function StripSlashes($str)
    {
        if(get_magic_quotes_gpc())
        {
            $str = stripslashes($str);
        }
        return $str;
    }

    private function HandleError($err)
    {
        $this->error_message .= $err."\r\n";
    }



}
?>
